﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Tools;

namespace DataHandler
{
    public class GetSql
    {
        /// <summary>
        /// 查询Oracle中雨量平均值与最大雨量 and t.drp<500 去掉乱数据
        /// </summary>
        /// <param name="datetime">当前时间</param>
        /// <returns></returns>
        public DataTable GetYiChangRain(string datetime) 
        {
            DateTime date1 = DateTime.Parse(datetime);
            string datetime2 = date1.AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss");//当天0~8点，取昨天8点到当前的数据
            int hour = DateTime.Now.Hour;//当前小时
            string sql_ora = "";
            //[0,8)||[8,9]||>9点
            if (hour >= 0 && hour < 8)
            {
                sql_ora = string.Format(@"select b.stnm,t.stcd,sum(t.drp) sumval from st_pptn_r t left join st_stbprp_b b on t.stcd=b.stcd
            where to_char(t.tm,'yyyy-MM-dd HH24:mi:ss')>='{0}' and b.admauth='宜昌水文' and t.drp is not null  and t.drp<500  group by  b.stnm,t.stcd order by sumval ", datetime2);
            }
            else
            {
                sql_ora = string.Format(@"select b.stnm,t.stcd,sum(t.drp) sumval from st_pptn_r t left join st_stbprp_b b on t.stcd=b.stcd
            where to_char(t.tm,'yyyy-MM-dd HH24:mi:ss')>='{0}' and b.admauth='宜昌水文' and t.drp is not null  and t.drp<500  group by  b.stnm,t.stcd order by sumval ", datetime);
            }

            
            DataTable dt_rain_ora = new DataTable();
            dt_rain_ora = OracleHelper.Query(OracleHelper.Oracleconn, sql_ora).Tables[0];
            return dt_rain_ora;
        }
        /// <summary>
        /// 重要江河水情查询
        /// </summary>
        /// <returns></returns>
        public DataTable GetImportantJiangHeData() 
        {
            //江河水情表在Oracle中，目前只找到河道水情表ST_RIVER_R(查询指定ZQ不为空站点的当天最新一条数据）and b.admauth='宜昌水文'
            string sql_ora = string.Format(@"select a1.* from
            (select b.stnm,t.tm,t.stcd,t.z,t.q from st_river_r t right join st_stbprp_b b on t.stcd=b.stcd  left join st_stimp_b c on t.stcd=c.stcd
            where to_char(t.tm,'yyyy-MM-dd')='{0}' and b.sttp='ZQ'  and c.sttp='ZQ' and t.q is not null  order by t.tm desc, b.stnm) a1
            right join
            (select  t1.stcd, max(t1.tm)  maxtime from st_river_r t1 where to_char(t1.tm,'yyyy-MM-dd')='{0}' group by t1.stcd) a2
            on a1.stcd=a2.stcd and a1.tm=a2.maxtime  where a1.stnm is not null and a1.Z is not null and a1.q>=0", DateTime.Now.ToString("yyyy-MM-dd"));
            return OracleHelper.Query(OracleHelper.Oracleconn, sql_ora).Tables[0];
        }
        /// <summary>
        /// 重要水库水情
        /// </summary>
        /// <returns></returns>
        public DataTable GetImportantShuiKuData() 
        {
            //江河水情表在Oracle中河道水情表ST_RIVER_R(查询指定RR不为空站点的当天最新一条数据）and b.admauth='宜昌水文'
            string sql_ora = string.Format(@"select a1.* from
            (select b.stnm,t.tm,t.stcd,t.RZ,t.W from st_rsvr_r t right join st_stbprp_b b on t.stcd=b.stcd   left join st_stimp_b c on t.stcd=c.stcd
             where to_char(t.tm,'yyyy-MM-dd')='{0}'  and b.sttp='RR'  and c.sttp='RR'  and t.W is not null  order by t.tm desc, b.stnm) a1
             right join
            (select  t1.stcd, max(t1.tm)  maxtime from st_rsvr_r t1 where to_char(t1.tm,'yyyy-MM-dd')='{0}' group by t1.stcd) a2
            on a1.stcd=a2.stcd and a1.tm=a2.maxtime  where a1.stnm is not null and a1.W>=0 and a1.RZ is not null", DateTime.Now.ToString("yyyy-MM-dd"));
            DataTable dt = new DataTable();
            return OracleHelper.Query(OracleHelper.Oracleconn, sql_ora).Tables[0];
        }
        /// <summary>
        /// 全市城区积水预警信息（只给今天的数据，避免脏数据太多）
        /// </summary>
        /// <returns></returns>
        public DataTable GetCityWaterWarning() 
        {
            //全市城区积水预警信息。站点名：monitoring_point_name 值：[value]
            string sql = string.Format(@"SELECT A.* from
            (SELECT t1.monitoring_point_name 
            ,t.[monitoring_mn]
            ,t.[factor_id]
            ,t.[datatime]
            ,t.[value]
            ,t.[flag]
            FROM [dbo].[water_alarm] t left join dbo.monitoring_points_info t1 on t.monitoring_mn=t1.monitoring_point_mn 
            where t.factor_id='Z') A
            right join 
           (SELECT [monitoring_mn]
           ,MAX([datatime]) [datatime]
            FROM [NFW].[dbo].[water_alarm] where [factor_id]='Z' group by [monitoring_mn]) B
            on A.[monitoring_mn]=B.[monitoring_mn] and A.[datatime]=B.[datatime]
            where A.datatime>='{0}'", DateTime.Now.ToString("yyyy-MM-dd"));
            return SqlHelper.Query(SqlHelper.NFW, CommandType.Text, sql, null).Tables[0];
        }
        /// <summary>
        /// 地下埋深
        /// </summary>
        /// <returns></returns>
        public DataTable GetDiXiaMaiShen() 
        {
            //地下埋深：GWBD；地下水势：GWPTN
            string sql_ora = string.Format(@"select A.* from 
            (select  b.stnm,t.stcd,t.tm,t.gwbd,t.gwptn,b.lgtd,b.lttd from st_grw_r t left join  st_stbprp_b b on t.stcd=b.stcd where b.admauth='宜昌水文') A
             right join 
            (select t1.stcd,max(t1.tm) tm from st_grw_r t1 group by t1.stcd) A1 
            on A.STCD=A1.STCD and A.TM=A1.tm");
            return OracleHelper.Query(OracleHelper.Oracleconn, sql_ora).Tables[0];
        }
        /// <summary>
        /// 土壤墒情
        /// </summary>
        /// <returns></returns>
        public DataTable GetTuRangShangQing() 
        {
            //查询每个站点最新一条垂直平均含水量的值
            string sql_ora = string.Format(@"select distinct A.* from 
            (select  b.stnm,t.stcd,t.tm,t.vtavslm,b.lgtd,b.lttd from st_soil_r t left join  st_stbprp_b b on t.stcd=b.stcd where b.admauth='宜昌水文') A
             right join 
            (select t1.stcd,max(t1.tm) tm from st_soil_r t1 group by t1.stcd) A1 
            on A.STCD=A1.STCD and A.TM=A1.tm");
            return OracleHelper.Query(OracleHelper.Oracleconn, sql_ora).Tables[0];
        }
        /// <summary>
        /// 全部水库水情
        /// </summary>
        /// <returns></returns>
        public DataTable GetShuiKuShuiQing() 
        {
            string sql_ora = string.Format(@"select distinct A.*,A2.Fsltdz from 
            (select  b.stnm,t.stcd,t.tm,t.rz,t.w,b.lgtd,b.lttd from st_rsvr_r t left join  st_stbprp_b b on t.stcd=b.stcd where  b.sttp='RR' and b.admauth='宜昌水文') A
             right join 
            (select t1.stcd,max(t1.tm) tm from st_rsvr_r t1 group by t1.stcd) A1 
            on A.STCD=A1.STCD and A.TM=A1.tm
            left join st_rsvrfsr_b A2 on A1.STCD=A2.STCD 
            where A.STNM is not null");
            return OracleHelper.Query(OracleHelper.Oracleconn, sql_ora).Tables[0];
        }
        /// <summary>
        /// 江河水情
        /// </summary>
        /// <returns></returns>
        public DataTable GetJiangheShuiQing() 
        {
            //ZZ,ZQ的站点
            string sql_ora = string.Format(@"select distinct A.stnm,A.stcd,A.z,A2.WRZ,A2.FRZ,A2.GRZ,A.q,A2.WRQ,A2.FRQ,A2.GRQ,A.wptn,A.atcunit,A.lgtd,A.lttd from
            (select b.stnm,t.stcd,t.tm,t.z,t.q,t.wptn,b.atcunit,b.lgtd,b.lttd from st_river_r t left join st_stbprp_b b on t.stcd=b.stcd where  b.sttp in ('ZZ','ZQ')  and b.admauth='宜昌水文') A
             right join 
            (select t1.stcd,max(t1.tm) tm from st_river_r t1 group by t1.stcd) A1 
             on A.stcd=A1.stcd and A.tm=A1.tm
             left join st_rvfcch_b A2 on A.stcd=A2.STCD
             where A.stnm is not null
             order by A.atcunit ");
            return OracleHelper.Query(OracleHelper.Oracleconn, sql_ora).Tables[0];
        }
        /// <summary>
        /// 城市积水
        /// </summary>
        /// <returns></returns>
        public DataTable GetCityJiShui() 
        {
            string sql_ora = string.Format(@"SELECT B.[monitoring_point_name],A.[ST],A.[TT]
            ,'Z'=case
            when A.[Z]<0 then 0
            else A.[Z]
            end
            ,'flag'=Case
            when C.[flag] is null then '0'
            else C.[flag]
            end
            ,B.[monitoring_point_longitude] lgtd
            ,B.[monitoring_point_latitude] lttd
            FROM [NFW].[dbo].[TempData] A 
            left join [NFW].[dbo].[monitoring_points_info] B on A.ST=B.[monitoring_point_mn]
            left join [NFW].[dbo].[water_alarm] C on A.ST=C.[monitoring_mn] and A.TT=C.[datatime]
            where B.[monitoring_point_type]='2' ");
            return SqlHelper.Query(SqlHelper.NFW, CommandType.Text, sql_ora, null).Tables[0];
        }
        /// <summary>
        /// 城市河道
        /// </summary>
        /// <returns></returns>
        public DataTable GetChengShihedao() 
        {
            string sql_sql = string.Format(@" SELECT B.[monitoring_point_name] ,A.[ST],A.[TT]
            ,'Z'=case
            when A.[Z]<0 then 0
            else A.[Z]
            end
            ,D.oldZ
            ,'flag'=Case
            when C.[flag] is null then '0'
            else C.[flag]
            end
            ,B.[monitoring_point_longitude] lgtd
            ,B.[monitoring_point_latitude] lttd
            ,B.RVNM
            FROM [NFW].[dbo].[TempData] A 
            left join [NFW].[dbo].[monitoring_points_info] B on A.ST=B.[monitoring_point_mn]
            left join [NFW].[dbo].[water_alarm] C on A.ST=C.[monitoring_mn] and A.TT=C.[datatime]
            left join (SELECT [monitoring_mn],[avgvalue] oldZ
            FROM [NFW].[dbo].[water_emstime] where [factor_id]='Z' 
            and [datatime]=(select top 1 dateadd(MI,-5,TT) TT from [NFW].[dbo].[TempData])) D
            on A.ST=D.[monitoring_mn]
            where B.[monitoring_point_type]='1'");
            return SqlHelper.Query(SqlHelper.NFW, CommandType.Text, sql_sql, null).Tables[0];
        }
        /// <summary>
        /// 县区平均雨量
        /// </summary>
        /// <param name="datetime">当前时间</param>
        /// <returns></returns>
        public DataTable GetPingJunYuLiang(string datetime) 
        {
            DateTime date1 = DateTime.Parse(datetime);
            string datetime2 = date1.AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss");//当天0~8点，取昨天8点到当前的数据
            int hour = DateTime.Now.Hour;//当前小时
            string sql_ora = "";
            //[0,8)||[8,9]||>9点
            if (hour >= 0 && hour < 8) 
            {
                sql_ora = string.Format(@"select atcunit,addvcd,sum(sumval)  sumrain,count(stcd)  numrain from
            (select a.stnm,a.stcd,a.sumval,a.atcunit,a.addvcd from
            (select b.stnm,t.stcd,sum(t.drp) over(partition by t.stcd order by t.stcd) sumval,b.atcunit,b.addvcd from st_pptn_r t left join st_stbprp_b b on t.stcd=b.stcd
            where to_char(t.tm,'yyyy-MM-dd HH24:mi:ss')>='{0}' and b.admauth='宜昌水文' order by b.addvcd) a where a.sumval is not null  group by a.stnm,a.stcd,a.sumval,a.atcunit,a.addvcd)
            group by atcunit,addvcd order by addvcd", datetime2);
            }
            else
            {
                sql_ora = string.Format(@"select atcunit,addvcd,sum(sumval)  sumrain,count(stcd)  numrain from
            (select a.stnm,a.stcd,a.sumval,a.atcunit,a.addvcd from
            (select b.stnm,t.stcd,sum(t.drp) over(partition by t.stcd order by t.stcd) sumval,b.atcunit,b.addvcd from st_pptn_r t left join st_stbprp_b b on t.stcd=b.stcd
            where to_char(t.tm,'yyyy-MM-dd HH24:mi:ss')>='{0}' and b.admauth='宜昌水文' order by b.addvcd) a where a.sumval is not null  group by a.stnm,a.stcd,a.sumval,a.atcunit,a.addvcd)
            group by atcunit,addvcd order by addvcd", datetime);
            }

            
            return  OracleHelper.Query(OracleHelper.Oracleconn, sql_ora).Tables[0];
        }

        /// <summary>
        /// 县区平均雨量详情
        /// </summary>
        /// <returns></returns>
        public DataTable GetPingJunYuLiangdetail(string datetime) 
        {
            DateTime date1 = DateTime.Parse(datetime);
            string datetime2 = date1.AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss");//当天0~8点，取昨天8点到当前的数据
            int hour = DateTime.Now.Hour;//当前小时
            string sql_ora = "";
            //[0,8)||[8,9]||>9点
            if (hour >= 0 && hour < 8)
            {
                sql_ora = string.Format(@"select a.stnm,a.stcd,a.sumval,a.atcunit,a.addvcd from
            (select b.stnm,t.stcd,sum(t.drp) over(partition by t.stcd order by t.stcd) sumval,b.atcunit,b.addvcd from st_pptn_r t left join st_stbprp_b b on t.stcd=b.stcd
            where to_char(t.tm,'yyyy-MM-dd HH24:mi:ss')>='{0}' and b.admauth='宜昌水文'  order by sumval desc) a where a.sumval is not null  group by a.stnm,a.stcd,a.sumval,a.atcunit,a.addvcd order by a.addvcd,a.sumval desc ,a.stcd ", datetime2);
            }
            else
            {
                sql_ora = string.Format(@"select a.stnm,a.stcd,a.sumval,a.atcunit,a.addvcd from
            (select b.stnm,t.stcd,sum(t.drp) over(partition by t.stcd order by t.stcd) sumval,b.atcunit,b.addvcd from st_pptn_r t left join st_stbprp_b b on t.stcd=b.stcd
            where to_char(t.tm,'yyyy-MM-dd HH24:mi:ss')>='{0}' and b.admauth='宜昌水文'  order by sumval desc) a where a.sumval is not null  group by a.stnm,a.stcd,a.sumval,a.atcunit,a.addvcd order by a.addvcd,a.sumval desc ,a.stcd ", datetime);
            }
            
            return OracleHelper.Query(OracleHelper.Oracleconn, sql_ora).Tables[0];
        }


        /// <summary>
        /// 所有站点信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllSite() 
        {
            string sql_ora = string.Format(@"select t.stcd,t.stnm,t.Lgtd,t.Lttd,t.stlc,t.sttp,t.atcunit  from st_stbprp_b t where t.admauth='宜昌水文' and t.lgtd is not null");
            return OracleHelper.Query(OracleHelper.Oracleconn, sql_ora).Tables[0];
        }
        /// <summary>
        /// 加雨量
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllSite1()
        {
            string datetime = DateTime.Now.ToString("yyyy-MM-dd") + " 08:00:00";
            DateTime date1 = DateTime.Parse(datetime);
            string datetime2 = date1.AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss");//当天0~8点，取昨天8点到当前的数据
            int hour = DateTime.Now.Hour;//当前小时
            string sql_ora = "";
            //[0,8)||[8,9]||>9点
            if (hour >= 0 && hour < 8)
            {
                sql_ora = string.Format(@"select a.stnm,a.stcd,a.sumval,a.atcunit,a.addvcd,a.lgtd,a.lttd,a.stlc,a.sttp from
            (select b.stnm,t.stcd,sum(t.drp) over(partition by t.stcd order by t.stcd) sumval,b.atcunit,b.addvcd,b.lgtd,b.lttd,b.stlc,b.sttp from st_pptn_r t left join st_stbprp_b b on t.stcd=b.stcd
            where to_char(t.tm,'yyyy-MM-dd HH24:mi:ss')>='{0}' and b.admauth='宜昌水文'  order by sumval desc) a where a.sumval is not null  group by a.stnm,a.stcd,a.sumval,a.atcunit,a.addvcd,a.lgtd,a.lttd,a.stlc,a.sttp order by a.addvcd,a.sumval desc ,a.stcd ", datetime2);
            }
            else
            {
                sql_ora = string.Format(@"select a.stnm,a.stcd,a.sumval,a.atcunit,a.addvcd,a.lgtd,a.lttd,a.stlc,a.sttp from
            (select b.stnm,t.stcd,sum(t.drp) over(partition by t.stcd order by t.stcd) sumval,b.atcunit,b.addvcd,b.lgtd,b.lttd,b.stlc,b.sttp from st_pptn_r t left join st_stbprp_b b on t.stcd=b.stcd
            where to_char(t.tm,'yyyy-MM-dd HH24:mi:ss')>='{0}' and b.admauth='宜昌水文'  order by sumval desc) a where a.sumval is not null  group by a.stnm,a.stcd,a.sumval,a.atcunit,a.addvcd,a.lgtd,a.lttd,a.stlc,a.sttp order by a.addvcd,a.sumval desc ,a.stcd ", datetime);
            }
            
            return OracleHelper.Query(OracleHelper.Oracleconn, sql_ora).Tables[0];
        }
        /// <summary>
        /// 点击地图marker显示其信息
        /// </summary>
        /// <returns></returns>
//        public DataTable ClickMapMarker(string datatime, string datatime1, string stcd) 
//        {
//            string sql_ora = string.Format(@"
//            select b.stnm,b.stcd,r.JRYL,r1.ZRYL,r2.z,r3.vtavslm,r4.gwbd,r5.rz,b.lgtd,b.lttd  from st_stbprp_b b 
//            --今日雨量
//            left join (select t.stcd,sum(t.drp) JRYL from st_pptn_r t where to_char(t.tm,'yyyy-MM-dd HH24:mi:ss')>'{0}'and t.stcd='{2}'group by t.stcd) r on b.stcd=r.stcd
//            --昨日雨量
//            left join (select t.stcd,t.dyp ZRYL  from st_pptn_r t where to_char(t.tm,'yyyy-MM-dd HH24:mi:ss')='{0}'and t.stcd='{2}' ) r1 on b.stcd=r1.stcd
//            --水位
//            left join (select t.stcd,t.z from st_river_r t where to_char(t.tm,'yyyy-MM-dd HH24:mi:ss')>='{1}'and t.stcd='{2}'and rownum=1) r2 on b.stcd=r2.stcd
//            --土壤墒情的含水量
//            left join (select t.stcd,t.vtavslm from st_soil_r t  where to_char(t.tm,'yyyy-MM-dd HH24:mi:ss')>='{1}'and t.stcd='{2}'and rownum=1) r3 on b.stcd=r3.stcd
//            --地下水埋深
//            left join (select t.stcd,t.gwbd from st_grw_r t  where to_char(t.tm,'yyyy-MM-dd HH24:mi:ss')>='{1}'and t.stcd='{2}'and rownum=1) r4 on b.stcd=r4.stcd
//            --库上水位
//            left join (select t.stcd,t.rz from st_rsvr_r t  where to_char(t.tm,'yyyy-MM-dd HH24:mi:ss')>='{1}'and t.stcd='{2}'and rownum=1) r5 on b.stcd=r5.stcd
//            where b.stcd='{2}'
//            ", datatime, datatime1, stcd);
//            return OracleHelper.Query(OracleHelper.Oracleconn, sql_ora).Tables[0];
//        }
    }
}
